package com.kaifamiao.www.XML.do0930;

import com.kaifamiao.www.XML.do0929.Student;
import com.kaifamiao.www.XML.do0929.Test1;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class Test {
    public static void main(String[] args) {

        InputStream inputStream = Test.class.getClassLoader().getResourceAsStream("SQL.xml");
        String sql = Sql(inputStream);
        System.out.println(select(sql));
    }

    public static String Sql(InputStream in){
        SAXReader saxReader = new SAXReader();
        String sql = "";
        try {
            Document document = saxReader.read(in);
            Element rootElement = document.getRootElement();
            Element select = rootElement.element("select");
            sql = select.getTextTrim();
        } catch (DocumentException e) {
            e.printStackTrace();
        }
        return sql;
    }

    private static String url;
    private static String user;
    private static String password;
    static {
        InputStream inputStream = Test1.class.getClassLoader().getResourceAsStream("db.properties");
        Properties prop = new Properties();
        try {
            prop.load(inputStream);
            url = prop.getProperty("url");
            user = prop.getProperty("user");
            password = prop.getProperty("password");
            Class.forName(prop.getProperty("driver"));
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static List<Student> select (String sql){
        List<Student> studentList = new ArrayList<>();
        try (
                Connection connection = DriverManager.getConnection(url,user,password);
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
                ResultSet resultSet = preparedStatement.executeQuery();
        ){
            while (resultSet.next()){
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String nick = resultSet.getString(3);
                String gender = resultSet.getString(4);
                int age = resultSet.getInt(5);
                Student student = new Student();
                student.setId(id);
                student.setName(name);
                student.setNick(nick);
                student.setGender(gender);
                student.setAge(age);
                studentList.add(student);
            }
        }  catch (SQLException e) {
            e.printStackTrace();
        }
        return studentList;
    }
}
